import pymysql
import requests


def spider1():
    ## 执行创表函数
    create_table()
    url = 'https://movie.douban.com/j/chart/top_list?type=24&interval_id=100%3A90&action='
    param = {
        "limit": 20,
        "start": 0
    }
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.75 Safari/537.36"
    }
    response = requests.get(url=url, params=param, headers=headers)
    for i in response.json():
        ''' 数据格式：['憨豆先生精选辑', 'https://movie.douban.com/subject/5133063/', "['喜剧']"]  '''
        lists = [i['title'], i['url'], str(i['types'])]
        print(lists)
        ## 插入数据
        insert_table(lists)

'''
传递参数：无
功能：删除movies表。创建movies表。
'''
def create_table():
    ## 连接数据库
    db = pymysql.connect(host="127.0.0.1",user="root",password="jian",database="test",port=3306)
    ## 创建执行语句
    cursor = db.cursor()
    sql_DropTable = "drop table if exists movies"
    ## 执行sql语句
    cursor.execute(sql_DropTable)
    sql_CreateTable = """create table movies(
                id int primary key auto_increment,
                title varchar(50),
                url varchar(200),
                type varchar(150)
            )"""
    cursor.execute(sql_CreateTable)
    ## 提交sql语句
    db.commit()
    print("创建movies表...")
    ## 关闭连接
    db.close()
'''
传递参数：value(可以是一个三个字符串的列表，元祖)
功能：插入数据导入movies表。
'''
def insert_table(value):
    db = pymysql.connect(host="127.0.0.1",user="root",password="jian",database="test",port=3306,charset="utf8")
    cursor = db.cursor()
    sql = 'insert into movies(title,url,type) values(%s,%s,%s)'
    try:
        ## 执行sql语句，并传递参数
        cursor.execute(sql, value)
        db.commit()
        print("插入数据成功...")
    except:
        db.rollback()
        print("插入失败")
    db.close()

if __name__ == '__main__':
    spider1()
